package com.hnkywl.sts.dao.cwgl.pjdr;

import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-6-6
 * Time: 上午10:24
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XxfpdrDao extends SimpleHibernateTemplate{
    /**
     * 获得凭证主信息
     * @return
     */
/*    public Map findVoucherMain(){
        String sql = "select p.name  as \"vchrtypecode\", yg.xm as \"createuser\", sysdate as \"createdate\"" +
                " from t_sys_pzz p, t_sys_yg yg where p.deleted = 0 and yg.deleted = 0 and p.code = 2 and yg.id = ?";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        sqlQuery.setLong(0, YgService.getCurrentYg().getId());
        return (Map) sqlQuery.uniqueResult();
    }*/

    public List<Map> findVoucherItem(Long jsdId){
        String sql = getFl1Sql() + " union all " + getFl2Sql() + " union all " + getFl3Sql();
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setLong("jsdId",jsdId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    private String getFl1Sql(){
        String sql = "select a.*,b.*,c.*,1 as \"itemorder\" from (" +
                "select '应收'||kh.qc||'料款' as \"digest\",jsd.je as \"debit\" ,0 as \"credit\"" +
                "  from t_biz_jsd jsd left join t_sys_gsjg kh on (jsd.glqy_id = kh.id)" +
                " where jsd.type = 1 and jsd.id = :jsdId" +
                ") a,(" +
                "select cy.kmdm as \"subjectcode\", cy.jqcwkmmc as \"subjectname\"" +
                "   from t_sys_cykmdmys cy" +
                "   left join t_biz_jsd jsd on (cy.gsid = jsd.gs_id)" +
                "  where cy.codeid = 100 and jsd.type = 1 and jsd.id = :jsdId" +
                ") b,(" +
                "select ljkmys.khfzhsdm as \"customercode\",0 as \"qty\"" +
                "  from t_sys_ljkhdmys ljkmys" +
                "  left join t_biz_jsd jsd on (ljkmys.nbgsid = jsd.gs_id and ljkmys.qyjgid = jsd.glqy_id)" +
                "  where jsd.type = 1 and jsd.id = :jsdId and ljkmys.lx = 1" +
                ") c";
        return sql;
    }
    private String getFl2Sql(){
        String sql = "select a.*,b.*,c.*,2 as \"itemorder\" from (" +
                "select '应付'||gys.qc||'料款' as \"digest\", jsd.se as \"debit\" ,0 as \"credit\"" +
                "  from t_biz_jsd jsd left join t_sys_gsjg gys on (jsd.glqy_id = gys.id)" +
                " where jsd.id = :jsdId" +
                ") a,(" +
                "select cy.kmdm as \"subjectcode\", cy.jqcwkmmc as \"subjectname\"" +
                "  from t_sys_cykmdmys cy left join t_biz_jsd jsd on (cy.gsid = jsd.gs_id)" +
                " where cy.codeid = 900 and jsd.id = :jsdId and jsd.type = 1" +
                ") b,(select '' as \"suppliercode\",0 as \"qty\" from dual) c";
        return sql;
    }
    
    private String getFl3Sql(){
        String sql = "select a.*,b.*,c.*,3 as \"itemorder\" from (" +
                "select '应收'||kh.qc||'料款' as \"digest\",0 as \"debit\" ,jsd.me as \"credit\"" +
                "  from t_biz_jsd jsd" +
                "  left join t_sys_gsjg kh on (jsd.glqy_id = kh.id)" +
                " where jsd.type = 1 and jsd.id = :jsdId" +
                ") a,(" +
                "select cy.kmdm as \"subjectcode\", cy.jqcwkmmc as \"subjectname\"" +
                "  from t_sys_cykmdmys cy" +
                "  left join t_biz_jsd jsd on (cy.gsid = jsd.gs_id)" +
                " where cy.codeid = 1100" +
                "   and jsd.type = 1 and jsd.id = :jsdId" +
                ") b,(" +
                "select ljkmys.khfzhsdm as \"customercode\",0 as \"qty\"" +
                "  from t_sys_ljkhdmys ljkmys" +
                "  left join t_biz_jsd jsd on (ljkmys.nbgsid = jsd.gs_id and ljkmys.qyjgid = jsd.glqy_id)" +
                "  where jsd.type = 1 and jsd.id = :jsdId and ljkmys.lx = 1" +
                ") c";
        return sql;
    }
}
